Dash

Row

Total new hires

93

Total exits

47

Net change

46

Row

Movement by month

Movement by Region

New hires

Row

Avg New Hires YTD

10.33

New hire split by Employment

New hire split by Work Authorisation

Row

New hires by Country

New hires by Org

Attrition

Row

Avg Attrition YTD

5.88

Attrition split by Employment

Attrition split by Work Authorisation

Row

Attrition by Country

Attrition by Org

---
title: "HR Movement dashboard"
output: 
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: fill
    source_code: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
library(openxlsx)
library(tidyverse)
library(plotly)
library(ggmap)



#Reading regional files

americas=read.xlsx("https://people.bu.edu/kalathur/datasets/hr/Americas.xlsx", sheet = "data")

apac=read.xlsx("https://people.bu.edu/kalathur/datasets/hr/APAC.xlsx", sheet = "data")

europe=read.xlsx("https://people.bu.edu/kalathur/datasets/hr/Europe.xlsx",sheet = "data")


#Consolidating global data
global=rbind(americas,apac,europe)

#Read file containing country coordinates
country_coor=read.xlsx("https://people.bu.edu/kalathur/datasets/hr/country_coord.xlsx")

```

# Dash {data-icon="fa-globe"}

## Row {data-width="150"}

### Total new hires

```{r}
newhires=global %>% filter(`Type.of.movement`=="Entry") %>% nrow()
valueBox(value = newhires,icon = "fa-user-plus",caption = "New Hires",color = "green")
```

### Total exits

```{r}
exits=global %>% filter(`Type.of.movement`=="Exit") %>% nrow()
valueBox(value = exits,icon = "fa-user-times",caption = "Exits", color = "orange")
```

### Net change

```{r}
newhires=global %>% filter(`Type.of.movement`=="Entry") %>% nrow()
exits=global %>% filter(`Type.of.movement`=="Exit") %>% nrow()

netchange = (newhires-exits)

#If loop to have either up-arrow or down-arrow icon on valuebox based on the value of netchange
if(netchange>0){
  valueBox(value = netchange,icon = "fa-arrow-up",caption = "Net Change", color = "coral")
} else{
valueBox(value = netchange,icon = "fa-arrow-down",caption = "Net Change", color = "coral")}

```

## Row

### Movement by month

```{r}
h1=global %>% group_by(Month,`Type.of.movement`) %>% 
  summarise(count=n())

p1=plot_ly(data = h1,
        x=h1$Month,
        y=h1$count) %>% 
  add_lines(linetype = h1$`Type.of.movement`,
            data = h1$count,
            hoverinfo="text",
            text=paste(h1$count)) %>% 
  layout(xaxis=list(title="Month"),
         yaxis=list(title="Count")) 
p1

```

### Movement by Region

```{r}
#Count by month, entry/exit and country
h2=global %>% 
  group_by(Month,`Type.of.movement`,Country) %>% 
  summarise(count=n())

#Add long/lat info based on country name from countr_coor
h2=left_join(h2,country_coor[,2:4],by=c("Country"="name"))
#h2 is the table where I want the info
#country_coor[,2:4] selects only long, lat and name columns
# so that I can only add the long and lat cols to h2 after the join
#by=c() identifies the keys in both dataframes

p2=plot_geo(h2,locationmode="world") %>% 
  add_markers(x=h2$longitude,
              y=h2$latitude,
              size=h2$count,
              color=h2$`Type.of.movement`,
              hoverinfo="text",
              hovertext=paste(h2$`Type.of.movement`,": ",h2$count)) %>% 
  layout()


p2

```

# New hires {data-icon="fa-user-plus"}

## Row {data-height="250"}

### Avg New Hires YTD

```{r}
newhire_bymonth=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(Month) %>% 
  summarise(count=n())

avgnewhire=round(mean(newhire_bymonth$count),2)
  
valueBox(avgnewhire,icon = "fa-user-plus",caption = "Average monthly new hires",color = "green")
```

### New hire split by Employment

```{r}
h5=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(`Employment.type`) %>% 
  summarise(count=n())

p5=plot_ly(h5) %>% 
  add_pie(labels=h5$`Employment.type`,values=h5$count,hole=0.6)

p5
```

### New hire split by Work Authorisation

```{r}
h6=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(`Work.Authorisation`) %>% 
  summarise(count=n())

p6=plot_ly(h6) %>% 
  add_pie(labels=h6$`Work.Authorisation`,values=h6$count,hole=0.6)

p6

```

## Row

### New hires by Country

```{r}

#Summarise and group by Country
h3=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(Month,Country) %>% 
  summarise(count=n())

#Use spread to make the table ready for plots
h3=spread(h3,key = Country,value = count)


#Bar chart by country
p3=plot_ly(h3,
           x=h3$Month,
           hoverinfo="text") %>% 
  add_bars(y=h3$Argentina,
           name="Argentina",
           hovertext=paste(h3$Argentina)) %>% 
  add_bars(y=h3$Australia,
           name="Australia",
           hovertext=paste("Australia: ",h3$Australia)) %>% 
  add_bars(y=h3$Brazil,
           name="Brazil",
           hovertext=paste("Brazil: ",h3$Brazil)) %>%
  add_bars(y=h3$Canada,
           name="Canada",
           hovertext=paste("Canada: ",h3$Canada)) %>%
  add_bars(y=h3$India,
           name="India",
           hovertext=paste("India: ",h3$India)) %>%
  add_bars(y=h3$Romania,
           name="Romania",
           hovertext=paste("Romania: ",h3$Romania)) %>%
  add_bars(y=h3$USA,
           name="USA",
           hovertext=paste("USA",h3$USA))
  
  
p3
```

### New hires by Org

```{r}
#Create a summary grouped by Org
h4=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(Month,Org) %>% 
  summarise(count=n())

#Make the table plot-ready
h4=spread(h4,key = Org,value = count)

p4=plot_ly(data = h4,
           x=h4$Month,
           hoverinfo="text") %>% 
  add_bars(y=h4$Corporate,
           name="Corporate",
           hovertext=paste("Corporate: ",h4$Corporate)) %>% 
  add_bars(y=h4$Delivery,
           name="Delivery",
           hovertext=paste("Delivery: ",h4$Delivery)) %>% 
  add_bars(y=h4$DeliveryOps,
           name="DeliveryOps",
           hovertext=paste("DeliveryOps: ",h4$DeliveryOps)) %>% 
  add_bars(y=h4$Support,
           name="Support",
           hovertext=paste("Support: ",h4$Support))
p4
```

# Attrition {data-icon="fa-user-times"}

## Row {data-height="250"}

### Avg Attrition YTD

```{r}
attn_bymonth=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(Month) %>% 
  summarise(count=n())

avgattn=round(mean(attn_bymonth$count),2)
  
valueBox(avgattn,icon = "fa-user-times",caption = "Average monthly attrition",color = "orange")
```

### Attrition split by Employment

```{r}
h7=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(`Employment.type`) %>% 
  summarise(count=n())

p7=plot_ly(h7) %>% 
  add_pie(labels=h7$`Employment.type`,values=h7$count,hole=0.6)

p7
```

### Attrition split by Work Authorisation

```{r}
h8=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(`Work.Authorisation`) %>% 
  summarise(count=n())

p8=plot_ly(h8) %>% 
  add_pie(labels=h8$`Work.Authorisation`,values=h8$count,hole=0.6)

p8

```

## Row

### Attrition by Country

```{r}

#Summarise and group by Country
h9=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(Month,Country) %>% 
  summarise(count=n())

#Use spread to make the table ready for plots
h9=spread(h9,key = Country,value = count)


#Bar chart by country
p9=plot_ly(h9,
           x=h9$Month,
           hoverinfo="text") %>% 
  add_bars(y=h9$Australia,
           name="Australia",
           hovertext=paste("Australia: ",h9$Australia)) %>% 
  add_bars(y=h9$India,
           name="India",
           hovertext=paste("India: ",h9$India)) %>%
  add_bars(y=h9$Romania,
           name="Romania",
           hovertext=paste("Romania: ",h9$Romania)) %>%
  add_bars(y=h9$USA,
           name="USA",
           hovertext=paste("USA",h9$USA))
  
  
p9
```

### Attrition by Org

```{r}
#Create a summary grouped by Org
h10=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(Month,Org) %>% 
  summarise(count=n())

#Make the table plot-ready
h10=spread(h10,key = Org,value = count)

p10=plot_ly(data = h10,
           x=h10$Month,
           hoverinfo="text") %>% 
  add_bars(y=h10$Corporate,
           name="Corporate",
           hovertext=paste("Corporate: ",h10$Corporate)) %>% 
  add_bars(y=h10$Delivery,
           name="Delivery",
           hovertext=paste("Delivery: ",h10$Delivery)) %>% 
  add_bars(y=h10$DeliveryOps,
           name="DeliveryOps",
           hovertext=paste("DeliveryOps: ",h10$DeliveryOps)) %>% 
  add_bars(y=h10$Support,
           name="Support",
           hovertext=paste("Support: ",h10$Support))
p10
```